package com.cms.sync.service;

import java.util.ArrayList;
import java.util.List;

import com.cms.sync.model.ImportChannelSyncData;
import com.cms.sync.model.ImportCpSyncData;
import com.cms.sync.model.ImportSyncData;
import com.service.AbstractDateService;
import com.utils.dbcp.ConnectionManager;
import com.utils.dbcp.ModelGenerator;

public class SyncImportDataServiceImpl extends AbstractDateService implements SyncImportDataService {

	@Override
	public ImportSyncData getImportSyncDataByContent(ImportSyncData model) {
		String sql = "select *,'' bd from t_importsyncdata where" +
				" day='"+model.getDay()+"'" +
				" and hour='"+model.getHour()+"'" +
				" and minute='"+model.getMinute()+"'" +
				" and cpid='"+model.getCpid()+"'" +
				" and channelid='"+model.getChannelid()+"'" +
				" and appnumber='"+model.getAppnumber()+"'" +
				" and goodsid='"+model.getGoodsid()+"'" +
				" and source='"+model.getSource()+"'";
		ModelGenerator<ImportSyncData> dbcpmg = new ModelGenerator<ImportSyncData>(ImportSyncData.class);
		logger.info("[SQL] [method:getImportSyncDataByContent] ["+sql+"]");
		return dbcpmg.generate(base, sql);
	}

	@Override
	public boolean saveImportSyncData(List<ImportSyncData> list) {
		String [] sql = new String[list.size()];
		ImportSyncData model = null;
		for(int i=0;i<list.size();i++)
		{
			model = list.get(i);
			ImportSyncData  l = getImportSyncDataByContent(model);
			if(l!=null)
			{
				sql[i] = "update t_importsyncdata set " +
						"fee='"+(model.getFee()+l.getFee())+"'" +
						",newusers='"+(model.getNewusers()+l.getNewusers())+"'" +
						",activeuser='"+(model.getActiveuser()+l.getActiveuser())+"'" +
						",payingcustomers='"+(model.getPayingcustomers()+l.getPayingcustomers())+"'" +
						",paidnumber='"+(model.getPaidnumber()+l.getPaidnumber())+"'" +
						"where day='"+model.getDay()+"' " +
							  "and hour='"+model.getHour()+"' " +
							  "and minute='"+model.getMinute()+"' " +
							  "and cpid='"+model.getCpid()+"' " +
							  "and channelid='"+model.getChannelid()+"' " +
							  "and appnumber='"+model.getAppnumber()+"' " +
							  "and goodsid='"+model.getGoodsid()+"' " +
							  "and source='"+model.getSource()+"'";
			}else
			{
				model.setId(getTableId("t_importsyncdata"));
				sql[i] = "insert into t_importsyncdata (id,day,hour,minute,cpid,appnumber,channelid," +
						"goodsid,goodsname,operator,source,province,fee,newusers,activeuser,payingcustomers,paidnumber) values ('"+model.getId()+"'," +
						"'"+model.getDay()+"','"+model.getHour()+"','"+model.getMinute()+"','"+model.getCpid()+"'" +
						",'"+model.getAppnumber()+"','"+model.getChannelid()+"','"+model.getGoodsid()+"','"+
						model.getGoodsname()+"','"+model.getOperator()+"','"+
						model.getSource()+"','"+model.getProvince()+"','"+model.getFee()+
						"','"+model.getNewusers()+"','"+model.getActiveuser()+"','"+model.getPayingcustomers()+"','"+model.getPaidnumber()+"')";
			}
		}
		return ConnectionManager.getInstance().executeBatch(base, sql);
	}

	@Override
	public ImportChannelSyncData getImportChannelSyncDataByContent(
			ImportChannelSyncData model) {
		String sql = "select *,'' bd from t_importchannelsyncdata where" +
				" day='"+model.getDay()+"'" +
				" and hour='"+model.getHour()+"'" +
				" and minute='"+model.getMinute()+"'" +
				" and cpid='"+model.getCpid()+"' " +
				" and channelid='"+model.getChannelid()+"' " +
				" and appnumber='"+model.getAppnumber()+"'" +
				" and goodsid='"+model.getGoodsid()+"'" +
				" and source='"+model.getSource()+"'";
		ModelGenerator<ImportChannelSyncData> dbcpmg = new ModelGenerator<ImportChannelSyncData>(ImportChannelSyncData.class);
		logger.info("[SQL] [method:getImportChannelSyncDataByContent] ["+sql+"]");
		return dbcpmg.generate(base, sql);
	}

	@Override
	public ImportCpSyncData getImportCpSyncDataByContent(ImportCpSyncData model) {
		String sql = "select *,'' bd from t_importcpsyncdata where" +
				" day='"+model.getDay()+"'" +
				" and hour='"+model.getHour()+"'" +
				" and minute='"+model.getMinute()+"'" +
				" and cpid='"+model.getCpid()+"' " +
				" and channelid='"+model.getChannelid()+"' " +
				" and appnumber='"+model.getAppnumber()+"'" +
				" and goodsid='"+model.getGoodsid()+"'" +
				" and source='"+model.getSource()+"'";
		ModelGenerator<ImportCpSyncData> dbcpmg = new ModelGenerator<ImportCpSyncData>(ImportCpSyncData.class);
		logger.info("[SQL] [method:getImportCpSyncDataByContent] ["+sql+"]");
		return dbcpmg.generate(base, sql);
	}

	@Override
	public boolean saveImportChannelSyncData(List<ImportChannelSyncData> list) {
		String [] sql = new String[list.size()];
		ImportChannelSyncData model = null;
		for(int i=0;i<list.size();i++)
		{
			model = list.get(i);
			ImportChannelSyncData  l = getImportChannelSyncDataByContent(model);
			if(l!=null)
			{
				sql[i] = "update t_importchannelsyncdata set " +
						"fee='"+(model.getFee()+l.getFee())+"',newusers='"
						+(model.getNewusers()+l.getNewusers())+"',activeuser='"
						+(model.getActiveuser()+l.getActiveuser())+"',payingcustomers='"
						+(model.getPayingcustomers()+l.getPayingcustomers())+"',paidnumber='"+(model.getPaidnumber()+l.getPaidnumber())+"' where day='"+model.getDay()+"' " +
							  "and hour='"+model.getHour()+"' " +
							  "and minute='"+model.getMinute()+"' " +
							  "and cpid='"+model.getCpid()+"' " +
							  "and channelid='"+model.getChannelid()+"' " +
							  "and appnumber='"+model.getAppnumber()+"' " +
							  "and goodsid='"+model.getGoodsid()+"' " +
							  "and source='"+model.getSource()+"'";
			}else
			{
				model.setId(getTableId("t_importchannelsyncdata"));
				sql[i] = "insert into t_importchannelsyncdata (id,day,hour,minute,cpid,appnumber,channelid," +
						"goodsid,goodsname,operator,source,province,fee,newusers,activeuser,payingcustomers,paidnumber) values ('"+model.getId()+"'," +
						"'"+model.getDay()+"','"+model.getHour()+"','"+model.getMinute()+"','"+model.getCpid()+"'" +
						",'"+model.getAppnumber()+"','"+model.getChannelid()+"','"+model.getGoodsid()+"','"+
						model.getGoodsname()+"','"+model.getOperator()+"','"+
						model.getSource()+"','"+model.getProvince()+"','"+model.getFee()+
						"','"+model.getNewusers()+"','"+model.getActiveuser()+"','"+model.getPayingcustomers()+"','"+model.getPaidnumber()+"')";
			}
		}
		return ConnectionManager.getInstance().executeBatch(base, sql);
	}

	@Override
	public boolean saveImportCpSyncData(List<ImportCpSyncData> list) {
		String [] sql = new String[list.size()];
		ImportCpSyncData model = null;
		for(int i=0;i<list.size();i++)
		{
			model = list.get(i);
			ImportCpSyncData  l = getImportCpSyncDataByContent(model);
			if(l!=null)
			{
				sql[i] = "update t_importcpsyncdata set " +
						"fee='"+(model.getFee()+l.getFee())+"',newusers='"
						+(model.getNewusers()+l.getNewusers())+"',activeuser='"
						+(model.getActiveuser()+l.getActiveuser())+"',payingcustomers='"
						+(model.getPayingcustomers()+l.getPayingcustomers())+"',paidnumber='"+(model.getPaidnumber()+l.getPaidnumber())+"' where day='"+model.getDay()+"' " +
							  "and hour='"+model.getHour()+"' " +
							  "and minute='"+model.getMinute()+"' " +
							  "and cpid='"+model.getCpid()+"' " +
							  "and channelid='"+model.getChannelid()+"' " +
							  "and appnumber='"+model.getAppnumber()+"' " +
							  "and goodsid='"+model.getGoodsid()+"' " +
							  "and source='"+model.getSource()+"'";
			}else
			{
				model.setId(getTableId("t_importcpsyncdata"));
				sql[i] = "insert into t_importcpsyncdata (id,day,hour,minute,cpid,appnumber,channelid," +
						"goodsid,goodsname,operator,source,province,fee,newusers,activeuser,payingcustomers,paidnumber) values ('"+model.getId()+"'," +
						"'"+model.getDay()+"','"+model.getHour()+"','"+model.getMinute()+"','"+model.getCpid()+"'" +
						",'"+model.getAppnumber()+"','"+model.getChannelid()+"','"+model.getGoodsid()+"','"+
						model.getGoodsname()+"','"+model.getOperator()+"','"+
						model.getSource()+"','"+model.getProvince()+"','"+model.getFee()+
						"','"+model.getNewusers()+"','"+model.getActiveuser()+"','"+model.getPayingcustomers()+"','"+model.getPaidnumber()+"')";
			}
		}
		return ConnectionManager.getInstance().executeBatch(base, sql);
	}

	@Override
	public boolean saveImportSyncData(ImportSyncData model) {
		String sql="";
		ImportSyncData  l = getImportSyncDataByContent(model);
		if(l!=null)
		{
			sql = "update t_importsyncdata set " +
					"fee='"+(model.getFee()+l.getFee())+"'" +
					",newusers='"+(model.getNewusers()+l.getNewusers())+"'" +
					",activeuser='"+(model.getActiveuser()+l.getActiveuser())+"'" +
					",payingcustomers='"+(model.getPayingcustomers()+l.getPayingcustomers())+"'" +
					",paidnumber='"+(model.getPaidnumber()+l.getPaidnumber())+"'" +
					"where day='"+model.getDay()+"' " +
						  "and hour='"+model.getHour()+"' " +
						  "and minute='"+model.getMinute()+"' " +
						  "and cpid='"+model.getCpid()+"' " +
						  "and channelid='"+model.getChannelid()+"' " +
						  "and appnumber='"+model.getAppnumber()+"' " +
						  "and goodsid='"+model.getGoodsid()+"' " +
						  "and source='"+model.getSource()+"'";
		}else
		{
			model.setId(getTableId("t_importsyncdata"));
			sql = "insert into t_importsyncdata (id,day,hour,minute,cpid,appnumber,channelid," +
					"goodsid,goodsname,operator,source,province,fee,newusers,activeuser,payingcustomers,paidnumber) values ('"+model.getId()+"'," +
					"'"+model.getDay()+"','"+model.getHour()+"','"+model.getMinute()+"','"+model.getCpid()+"'" +
					",'"+model.getAppnumber()+"','"+model.getChannelid()+"','"+model.getGoodsid()+"','"+
					model.getGoodsname()+"','"+model.getOperator()+"','"+
					model.getSource()+"','"+model.getProvince()+"','"+model.getFee()+
					"','"+model.getNewusers()+"','"+model.getActiveuser()+"','"+model.getPayingcustomers()+"','"+model.getPaidnumber()+"')";
		}
		logger.info("[SQL] [method:saveImportSyncData] ["+sql+"]");
		return ConnectionManager.getInstance().execute(base, sql);
	}

	@Override
	public boolean saveImportCpSyncData(ImportCpSyncData model) {
		String sql="";
		ImportCpSyncData  l = getImportCpSyncDataByContent(model);
		if(l!=null)
		{
			sql = "update t_importcpsyncdata set " +
					"fee='"+(model.getFee()+l.getFee())+"',newusers='"
					+(model.getNewusers()+l.getNewusers())+"',activeuser='"
					+(model.getActiveuser()+l.getActiveuser())+"',payingcustomers='"
					+(model.getPayingcustomers()+l.getPayingcustomers())+"',paidnumber='"+(model.getPaidnumber()+l.getPaidnumber())+"' where day='"+model.getDay()+"' " +
						  "and hour='"+model.getHour()+"' " +
						  "and minute='"+model.getMinute()+"' " +
						  "and cpid='"+model.getCpid()+"' " +
						  "and channelid='"+model.getChannelid()+"' " +
						  "and appnumber='"+model.getAppnumber()+"' " +
						  "and goodsid='"+model.getGoodsid()+"' " +
						  "and source='"+model.getSource()+"'";
		}else
		{
			model.setId(getTableId("t_importcpsyncdata"));
			sql = "insert into t_importcpsyncdata (id,day,hour,minute,cpid,appnumber,channelid," +
					"goodsid,goodsname,operator,source,province,fee,newusers,activeuser,payingcustomers,paidnumber) values ('"+model.getId()+"'," +
					"'"+model.getDay()+"','"+model.getHour()+"','"+model.getMinute()+"','"+model.getCpid()+"'" +
					",'"+model.getAppnumber()+"','"+model.getChannelid()+"','"+model.getGoodsid()+"','"+
					model.getGoodsname()+"','"+model.getOperator()+"','"+
					model.getSource()+"','"+model.getProvince()+"','"+model.getFee()+
					"','"+model.getNewusers()+"','"+model.getActiveuser()+"','"+model.getPayingcustomers()+"','"+model.getPaidnumber()+"')";
		}
		logger.info("[SQL] [method:saveImportCpSyncData] ["+sql+"]");
		return ConnectionManager.getInstance().execute(base, sql);
	}

	@Override
	public boolean saveImportChannelSyncData(ImportChannelSyncData model) {
		String sql="";
		ImportChannelSyncData  l = getImportChannelSyncDataByContent(model);
		if(l!=null)
		{
			sql = "update t_importchannelsyncdata set " +
					"fee='"+(model.getFee()+l.getFee())+"',newusers='"
					+(model.getNewusers()+l.getNewusers())+"',activeuser='"
					+(model.getActiveuser()+l.getActiveuser())+"',payingcustomers='"
					+(model.getPayingcustomers()+l.getPayingcustomers())+"',paidnumber='"+(model.getPaidnumber()+l.getPaidnumber())+"' where day='"+model.getDay()+"' " +
						  "and hour='"+model.getHour()+"' " +
						  "and minute='"+model.getMinute()+"' " +
						  "and cpid='"+model.getCpid()+"' " +
						  "and channelid='"+model.getChannelid()+"' " +
						  "and appnumber='"+model.getAppnumber()+"' " +
						  "and goodsid='"+model.getGoodsid()+"' " +
						  "and source='"+model.getSource()+"'";
		}else
		{
			model.setId(getTableId("t_importchannelsyncdata"));
			sql = "insert into t_importchannelsyncdata (id,day,hour,minute,cpid,appnumber,channelid," +
					"goodsid,goodsname,operator,source,province,fee,newusers,activeuser,payingcustomers,paidnumber) values ('"+model.getId()+"'," +
					"'"+model.getDay()+"','"+model.getHour()+"','"+model.getMinute()+"','"+model.getCpid()+"'" +
					",'"+model.getAppnumber()+"','"+model.getChannelid()+"','"+model.getGoodsid()+"','"+
					model.getGoodsname()+"','"+model.getOperator()+"','"+
					model.getSource()+"','"+model.getProvince()+"','"+model.getFee()+
					"','"+model.getNewusers()+"','"+model.getActiveuser()+"','"+model.getPayingcustomers()+"','"+model.getPaidnumber()+"')";
		}
		logger.info("[SQL] [method:saveImportChannelSyncData] ["+sql+"]");
		return ConnectionManager.getInstance().execute(base, sql);
	}

	@Override
	public String getMessageByrelevanceIdAndpointCode(String relevanceId, String pointCode) {
		String sql="select concat_ws(',',av.appversionnumber,convert(av.cpid,char),convert(apof.id,char),apof.pointofbillingname ) "+
						"from t_apppointofbilling apof,t_appversioninfo av,t_apppointterrace apt,t_appversionterrace apvt "+
							"where av.id=apt.appversionid and av.id=apvt.appversionid and apt.terraceid=apvt.terraceid and apt.apppointid=apof.id and apt.pointcode='"+pointCode+"' and apvt.relevanceid='"+relevanceId+"'"+
								"group by av.appnumber,av.cpid,apof.id ,apof.pointofbillingname";
		logger.info("[SQL] [method:getMessageByrelevanceIdAndpointCode] ["+sql+"]");
		ModelGenerator<String> model = new ModelGenerator<String>(String.class);
		return model.generate(base,sql.toString());
	}

	@Override
	public List<ImportSyncData> queryImportSyncDataByContent(String startdate,
			String enddate, int cpid, String appnumber, int channelid,
			String bd, String goodsname, String operator, String source,
			String province, String dimension) {
		String sql = "";
		{
			String gb = "";
			List<String> fl = new ArrayList<String>();
			
			if (!checkQueryDimension(dimension, "day"))
				gb += "0 ";
			else
				fl.add("day");
			gb += "day,";
			
			if (!checkQueryDimension(dimension, "appid"))
				gb += "'' ";
			else
				fl.add("appnumber");
			gb += "appnumber,";
			
			if (!checkQueryDimension(dimension, "cpid"))
				gb += "-1 ";
			else
				fl.add("cpid");
			gb += "cpid,";
			
			if (!checkQueryDimension(dimension, "channelid"))
				gb += "-1 ";
			else
				fl.add("channelid");
			gb += "channelid,";
			
			if (!checkQueryDimension(dimension, "bd"))
				gb += "'' ";
			else
				fl.add("bd");
			gb += "bd,";
			
			if (!checkQueryDimension(dimension, "goodsname"))
				gb += "'' ";
			else
				fl.add("goodsname");
			gb += "goodsname,";
			
			if (!checkQueryDimension(dimension, "operator"))
				gb += "'' ";
			else
				fl.add("operator");
			gb += "operator,";
			
			if (!checkQueryDimension(dimension, "source"))
				gb += "'' ";
			else
				fl.add("source");
			gb += "source,";
									
			if (!checkQueryDimension(dimension, "province"))
				gb += "'' ";
			else
				fl.add("province");
			gb += "province,";
			
			sql = "select 0 id,0 datafileid," + gb + "fee,newusers,activeuser,payingcustomers,paidnumber " +
				"from (select " + fl.toString().replace("[", "").replace("]", "").replace(" ", "") +
				",sum(fee) fee,sum(newusers) newusers,sum(activeuser) activeuser,sum(payingcustomers) payingcustomers,sum(paidnumber) paidnumber from t_importsyncdata t1,t_channel t2 " +
				"where t1.channelid=t2.id and (day>=" + startdate.replace("-", "")  + " " +
				"and day<=" + enddate.replace("-", "") + ") ";
			
			if (cpid > 0)
				sql += " and cpid='" + cpid + "' ";
			 if (appnumber != null && !appnumber.equals(""))
					sql += "and appnumber='" + appnumber +"' ";
			if (channelid > 0)
				sql += " and channelid='" + channelid + "' ";
			
			if ( bd!= null && !bd.equals(""))
				sql += "and bd='" + bd +"' ";
			
			if (goodsname != null && !goodsname.equals(""))
				sql += "and goodsname='" + goodsname +"' ";
			
			if (operator != null && !operator.equals(""))
				sql += "and operator='" + operator +"' ";
			
			if (source != null && !source.equals(""))
				sql += "and source='" + source +"' ";
			
			if (province != null && !province.equals(""))
				sql += "and province='" + province +"' ";
			
			sql += "group by " + fl.toString().replace("[", "").replace("]", "").replace(" ", "") + ") a " +
					"order by day,cpid,channelid,operator,province";
		}
		logger.info("[Sql] [method:queryImportSyncDataByContent] ["+sql+"]");
		ModelGenerator<ImportSyncData> dbcpmg = new ModelGenerator<ImportSyncData>(ImportSyncData.class);
		return dbcpmg.generateList(base, sql);
	}

	@Override
	public List<ImportCpSyncData> queryImportCpSyncDataByContent(String startdate,
			String enddate, int cpid, String appnumber, int channelid,
			String bd, String goodsname, String operator, String source,
			String province, String dimension) {
		String sql = "";
		{
			String gb = "";
			List<String> fl = new ArrayList<String>();
			
			if (!checkQueryDimension(dimension, "day"))
				gb += "0 ";
			else
				fl.add("day");
			gb += "day,";
			
			if (!checkQueryDimension(dimension, "appid"))
				gb += "'' ";
			else
				fl.add("appnumber");
			gb += "appnumber,";
			
			if (!checkQueryDimension(dimension, "cpid"))
				gb += "-1 ";
			else
				fl.add("cpid");
			gb += "cpid,";
			
			if (!checkQueryDimension(dimension, "channelid"))
				gb += "-1 ";
			else
				fl.add("channelid");
			gb += "channelid,";
			
			if (!checkQueryDimension(dimension, "bd"))
				gb += "'' ";
			else
				fl.add("bd");
			gb += "bd,";
			
			if (!checkQueryDimension(dimension, "goodsname"))
				gb += "'' ";
			else
				fl.add("goodsname");
			gb += "goodsname,";
			
			if (!checkQueryDimension(dimension, "operator"))
				gb += "'' ";
			else
				fl.add("operator");
			gb += "operator,";
			
			if (!checkQueryDimension(dimension, "source"))
				gb += "'' ";
			else
				fl.add("source");
			gb += "source,";
			
			if (!checkQueryDimension(dimension, "province"))
				gb += "'' ";
			else
				fl.add("province");
			gb += "province,";
			
			sql = "select 0 id,0 datafileid," + gb + "fee,newusers,activeuser,payingcustomers,paidnumber " +
				"from (select " + fl.toString().replace("[", "").replace("]", "").replace(" ", "") +
				",sum(fee) fee,sum(newusers) newusers,sum(activeuser) activeuser,sum(payingcustomers) payingcustomers,sum(paidnumber) paidnumber from t_importcpsyncdata t1,t_channel t2 " +
				"where t1.channelid=t2.id and (day>=" + startdate.replace("-", "")  + " " +
				"and day<=" + enddate.replace("-", "") + ") ";
			
			if (cpid > 0)
				sql += " and cpid='" + cpid + "' ";
			 if (appnumber != null && !appnumber.equals(""))
					sql += "and appnumber='" + appnumber +"' ";
			if (channelid > 0)
				sql += " and channelid='" + channelid + "' ";
			
			if (bd != null && !bd.equals(""))
				sql += "and bd='" + bd +"' ";
			
			if (goodsname != null && !goodsname.equals(""))
				sql += "and goodsname='" + goodsname +"' ";
			
			if (operator != null && !operator.equals(""))
				sql += "and operator='" + operator +"' ";
			
			if (source != null && !source.equals(""))
				sql += "and source='" + source +"' ";
			 
			if (province != null && !province.equals(""))
				sql += "and province='" + province +"' ";
			
			sql += "group by " + fl.toString().replace("[", "").replace("]", "").replace(" ", "") + ") a " +
					"order by day,cpid,operator,province";
		}
		ModelGenerator<ImportCpSyncData> dbcpmg = new ModelGenerator<ImportCpSyncData>(ImportCpSyncData.class);
		return dbcpmg.generateList(base, sql);
	}

	@Override
	public List<ImportChannelSyncData> queryImportChannelSyncDataByContent(
			String startdate, String enddate, int cpid, String appnumber,
			int channelid, String bd, String goodsname, String operator,
			String source, String province, String dimension) {
		String sql = "";
		{
			String gb = "";
			List<String> fl = new ArrayList<String>();
			
			if (!checkQueryDimension(dimension, "day"))
				gb += "0 ";
			else
				fl.add("day");
			gb += "day,";
			
			if (!checkQueryDimension(dimension, "appid"))
				gb += "'' ";
			else
				fl.add("appnumber");
			gb += "appnumber,";
			
			if (!checkQueryDimension(dimension, "cpid"))
				gb += "-1 ";
			else
				fl.add("cpid");
			gb += "cpid,";
			
			if (!checkQueryDimension(dimension, "channelid"))
				gb += "-1 ";
			else
				fl.add("channelid");
			gb += "channelid,";
			
			if (!checkQueryDimension(dimension, "bd"))
				gb += "'' ";
			else
				fl.add("bd");
			gb += "bd,";
			
			if (!checkQueryDimension(dimension, "goodsname"))
				gb += "'' ";
			else
				fl.add("goodsname");
			gb += "goodsname,";
			
			if (!checkQueryDimension(dimension, "operator"))
				gb += "'' ";
			else
				fl.add("operator");
			gb += "operator,";
			
			if (!checkQueryDimension(dimension, "source"))
				gb += "'' ";
			else
				fl.add("source");
			gb += "source,";
			
			
			
			if (!checkQueryDimension(dimension, "province"))
				gb += "'' ";
			else
				fl.add("province");
			gb += "province,";
			
			sql = "select 0 id,0 datafileid," + gb + "fee,newusers,activeuser,payingcustomers,paidnumber " +
				"from (select " + fl.toString().replace("[", "").replace("]", "").replace(" ", "") +
				",sum(fee) fee,sum(newusers) newusers,sum(activeuser) activeuser,sum(payingcustomers) payingcustomers,sum(paidnumber) paidnumber from t_importchannelsyncdata t1,t_channel t2 " +
				"where t1.channelid=t2.id and (day>=" + startdate.replace("-", "")  + " " +
				"and day<=" + enddate.replace("-", "") + ") ";
			
			if (cpid > 0)
				sql += " and cpid='" + cpid + "' ";
			 if (appnumber != null && !appnumber.equals(""))
					sql += "and appnumber='" + appnumber +"' ";
			if (channelid > 0)
				sql += " and channelid='" + channelid + "' ";
			
			if (bd != null && !bd.equals(""))
				sql += "and bd='" + bd +"' ";
			
			if (goodsname != null && !goodsname.equals(""))
				sql += "and goodsname='" + goodsname +"' ";
			
			if (operator != null && !operator.equals(""))
				sql += "and operator='" + operator +"' ";
			
			if (source != null && !source.equals(""))
				sql += "and source='" + source +"' ";
			
			if (province != null && !province.equals(""))
				sql += "and province='" + province +"' ";
			
			sql += "group by " + fl.toString().replace("[", "").replace("]", "").replace(" ", "") + ") a " +
					"order by day,channelid,operator,province";
		}
		logger.info("[SQL] [method:queryImportChannelSyncDataByContent] [sql:"+sql+"]");
		ModelGenerator<ImportChannelSyncData> dbcpmg = new ModelGenerator<ImportChannelSyncData>(ImportChannelSyncData.class);
		return dbcpmg.generateList(base, sql);
	}
}
